package com.zhf.dao;

import com.zhf.entity.Category;
import com.zhf.entity.Product;
import com.zhf.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author: ZhaoHuanfeng
 * @Date: 2020/9/27 15:35
 */
public class ProductDao {

    //需求1: 根据商品ID 获取商品名称 ,商品价格 以及商品所属分类的名称
    public Product findProductByID(String pid) throws SQLException {
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //编写SQL
        String sql="select * from product where pid=?";

        //执行查询
        Product product = queryRunner.query(sql, new BeanHandler<Product>(Product.class), pid);

        //获取外键的值，对应分类的id
        String cid=product.getCid();

        //获取到对应的商品分类的信息
        Category category = findCategoryByID(cid);

        //将对应的商品分类详细信息填加到product中
        product.setCategory(category);

        return product;
    }

    //根据分类ID 查询分类的详细信息
    public Category findCategoryByID(String cid) throws SQLException {
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //编写SQL
        String sql="select * from category where cid=?";

        //执行查询
        Category category = queryRunner.query(sql, new BeanHandler<Category>(Category.class),cid);

        return category;

    }

    //需求3: 查询指定分类ID 下的商品个数
    public int getCount(String cid) throws SQLException {
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //编写SQL
        String sql="select count(*) from product where cid=?";

        //执行SQL
        Long count = queryRunner.query(sql, new ScalarHandler<>(), cid);

        //将结果转换成int类型并返回
        return count.intValue();

    }

    //查询指定分类ID 下的所有商品信息
    public List<Product> findProductBycid(String cid) throws SQLException {
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //编写SQL
        String sql="select * from product where cid=?";

        //执行SQL
        List<Product> products = queryRunner.query(sql, new BeanListHandler<Product>(Product.class), cid);

        return products;
    }
}
